package com.ht.pms.core.service.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import com.ht.pms.core.util.HanyuPinyinHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.xml.sax.SAXException;

import com.ht.pms.core.common.cache.SalaryServiceCache;
import com.ht.pms.core.common.constant.SalaryConstants;
import com.ht.pms.core.common.constant.SalaryTemplateTableEnum;
import com.ht.pms.core.common.exception.CustomResultException;
import com.ht.pms.core.common.exception.ParamException;
import com.ht.pms.core.dao.SalaryBusinessDictDao;
import com.ht.pms.core.dao.SalaryTemplateDao;
import com.ht.pms.core.dao.SalaryTemplateDetailDao;
import com.ht.pms.core.dao.SalaryUploadFileDao;
import com.ht.pms.core.dao.SalaryUploadFileLogDao;
import com.ht.pms.core.dao.SalaryUserDao;
import com.ht.pms.core.dto.Result;
import com.ht.pms.core.dto.ReturnCodeEnum;
import com.ht.pms.core.dto.dict.DictParamsDto;
import com.ht.pms.core.dto.dict.DictRecordBaseDto;
import com.ht.pms.core.dto.poi.ExcelBean;
import com.ht.pms.core.dto.poi.ExcelExportQuerySqlReq;
import com.ht.pms.core.dto.poi.PoiParseHandler;
import com.ht.pms.core.dto.poi.SalaryUploadParam;
import com.ht.pms.core.dto.tempalte.Template;
import com.ht.pms.core.dto.upload.UploadFile;
import com.ht.pms.core.dto.upload.UploadFileExcelVo;
import com.ht.pms.core.dto.upload.UploadFileLog;
import com.ht.pms.core.dto.upload.UploadFileLogEnum;
import com.ht.pms.core.dto.user.UserInfoDto;
import com.ht.pms.core.handler.ExcelExportHandler;
import com.ht.pms.core.handler.SalaryValidateDataHandler;
import com.ht.pms.core.service.SalaryExcelDataService;
import com.ht.pms.core.util.PoiUtils;
import com.ht.pms.core.util.ValueSelector;

@Service
public class SalaryExcelDataServiceImpl implements SalaryExcelDataService {
	private static final Logger LOGGER = LoggerFactory.getLogger(SalaryExcelDataServiceImpl.class);

	private static final int PROCESS_BATCH_NUMBER = 500;

	private static final String SALARY_IMPORT_INSERT = "insert";
	private static final String SALARY_IMPORT_UPDATE = "update";

	@Autowired
	private SalaryUploadFileDao salaryUploadFileDao;

	@Autowired
	private SalaryUploadFileLogDao salaryUploadFileLogDao;

	@Autowired
	private SalaryBusinessDictDao salaryBusinessDictDao;

	@Autowired
	private SalaryValidateDataHandler validateHandler;

	@Autowired
	private SalaryServiceCache salaryServiceCache;

	@Autowired
	private SalaryTemplateDetailDao salaryTemplateDetailDao;

	@Autowired
	private SalaryUserDao salaryUserDao;
	
	@Autowired
	private SalaryTemplateDao salaryTemplateDao;
	
	@Transactional(rollbackFor=Exception.class)
	@Override
	public Result uploadSalaryFile(SalaryUploadParam req) throws Exception {
		UploadFileExcelVo param = validateHandler.validateSalaryUploadParam(req);
		String batchCode = param.getBatchCode();
		Integer fileId = param.getFileId();
		String templateCode = param.getTemplateCode();
		
		String tableName = SalaryTemplateTableEnum.getByTemplateType(param.getTemplateType()).getRelationTable();
		
		PoiParseHandler xlx = new PoiParseHandler() {

			private boolean effective = true;
			private int curRow = 0; // 当前行
			private int successNo = 0; // 成功数
			private int failNo = 0; // 失败数统计

			private int stateIndex = -1; // 导入状态in_state 字段索引
			private List<List<String>> insertContent = new ArrayList<>(); // 批次插入处理集
			private List<List<String>> updateContent = new ArrayList<>(); // 批次更新处理
			private List<String> titles = new ArrayList<>(); // 表头字段集
			private List<DictParamsDto> dictParams = null; // 模板字段集

			private List<UploadFileLog> fileLogs = new ArrayList<>();
			private Pattern pattern = Pattern.compile("^[+]?([0-9]+(.[0-9]{1,2})?)$");
			private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

			@Override
			public void optRows(int sheetIndex, int curRow, List<String> rowlist, boolean isEnd) throws SAXException {
				this.curRow = curRow;
				if (isEnd) {
					if (!insertContent.isEmpty()) {
						// 处理最后一批
						processBatchData(insertContent, SALARY_IMPORT_INSERT);
					}

					if (!updateContent.isEmpty()) {
						// 处理最后一批
						processBatchData(updateContent, SALARY_IMPORT_UPDATE);
					}
					// 根据fileId 查询所有成功数，失败数
					int successNumber = salaryUploadFileDao.selectSalaryFileRecord(fileId, tableName, SalaryConstants.SALARY_YES);
					int failNumber = salaryUploadFileDao.selectSalaryFileRecord(fileId, tableName, SalaryConstants.SALARY_NO);
					// 修改记录
					salaryUploadFileDao.updateSalaryFileRecord(successNumber, failNumber, fileId);
					
					// 删除用于重复校验的缓存
					salaryServiceCache.deleteIsExistAuthKey(fileId);

					if (!effective) {
						// 查询fileId 日志是否存在
						List<UploadFileLog> uploadFileLogs = salaryUploadFileLogDao.selectUploadFileLogByFileId(fileId);
						int ver = 1;
						if (uploadFileLogs != null && !uploadFileLogs.isEmpty()) {
							ver = uploadFileLogs.get(0).getVersion() + 1;
						}
						// 批量处理
						if (fileLogs != null) {
							int index = 0;
							if (fileLogs.size() <= PROCESS_BATCH_NUMBER) {
								while (index < (fileLogs.size() - PROCESS_BATCH_NUMBER)) {
									salaryUploadFileLogDao.insertUploadFileLog(
											fileLogs.subList(index, index + PROCESS_BATCH_NUMBER), ver);
								}
							}
							salaryUploadFileLogDao.insertUploadFileLog(fileLogs.subList(index, fileLogs.size()), ver);
						}
						fileLogs.clear();
						LOGGER.info("[导入基础数据] 导入数据部分失败，请查看相关日志返回！");
						throw new CustomResultException(
								String.format("导入详情，成功%s条、失败%s条，具体请查看日志详情！", successNo, failNo));
					}
					return;
				}

				/** 检查这一行是否是空行 */
				boolean isEmpty = true;
				for (String rowData : rowlist) {
					if (StringUtils.isNotEmpty(rowData)) {
						// 只要有一个不为空表明此行不为空
						isEmpty = false;
						break;
					}
				}
				if (isEmpty) {
					return;
				}

				if (titles.isEmpty()) {
					// 表头去空格，并转中文括号为英文括号
					for (String value : rowlist) {
						if(StringUtils.isBlank(value)) {
							LOGGER.info("[导入基础数据] 表头有空列，请检查！");
							throw new ParamException(ReturnCodeEnum.ERROR_PARAM.getReturnCode(),"表头有空列，请检查！");
						}
						String title = value.trim().replaceAll("（", "(").replaceAll("）", ")");
						titles.add(title);
					}
					// 校验并加入 数据状态字段
					this.dictParams = validateHandler.validateSalaryTitles(templateCode, titles, batchCode);
					titles.add(SalaryConstants.SALARY_IMPORT_RECORD_NAME);
					stateIndex = titles.indexOf(SalaryConstants.SALARY_IMPORT_RECORD_NAME);
					return;
				}

				// 校验一行数据
				checkRowData(rowlist);
				if (insertContent.size() == PROCESS_BATCH_NUMBER) {
					processBatchData(insertContent, SALARY_IMPORT_INSERT);
				}
				if (updateContent.size() == PROCESS_BATCH_NUMBER) {
					processBatchData(updateContent, SALARY_IMPORT_UPDATE);
				}

			}

			@Override
			public void checkRowData(List<String> rowlist) {
				int jobCodeIndex = titles.indexOf(SalaryConstants.SALARY_JOB_CODE_NAME);
				int numberIndex = titles.indexOf(SalaryConstants.SALARY_SEQUENCE_NUMBER_NAME);
				int emailIndex = titles.indexOf(SalaryConstants.SALARY_EMAIL_NAME);
				int nameIndex = titles.indexOf(SalaryConstants.SALARY_USER_NAME);
				String jobCode = rowlist.get(jobCodeIndex);
				
				String jobCodeColumn = null;
				String emailColumn = null;
				/** 检查这一行是否有效 */
				int inState = 1;

				/** 校验数据完整性及有效性 */
				for (int i = 0; i < titles.size(); i++) {
					/** 校验字段类型 */
					for (DictParamsDto dictParamDto : dictParams) {
						if(SalaryConstants.SALARY_JOB_CODE_NAME.equals(dictParamDto.getDictName())) {
							jobCodeColumn = dictParamDto.getDictCode();
						}
						if(SalaryConstants.SALARY_EMAIL_NAME.equals(dictParamDto.getDictName())) {
							emailColumn = dictParamDto.getDictCode();
						}
						
						if (titles.get(i).equals(dictParamDto.getDictName())) {
							String dictCode = dictParamDto.getDictCode();
							// 校验与表头同一列的字段值
							String value = rowlist.get(i);
							if (StringUtils.isBlank(value)) {
								// 判断是否必须
								if (dictParamDto.getIsMust() == SalaryConstants.SALARY_YES) {
									// 添加异常
									effective = false;
									inState = 0;
									String content = String.format("第%s行表头为%s的数据为空", curRow, titles.get(i));
									LOGGER.info("[数据导入] 数据导入失败： " + content);
									UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.DATA_NULL, fileId, batchCode,
											dictCode, jobCode, content);
									fileLogs.add(fileLog);

								}
							} else {
								// 判断类型,先获取字段类型
								try {
									switch (dictParamDto.getDictDataType()) {
									case SalaryConstants.INT_TYPE:
										Integer.valueOf(value);
										break;
									case SalaryConstants.STRING_TYPE:
										if(value.length() >= 45) {
											effective = false;
											inState = 0;
											String content = String.format("第[%s]行[%s]的数据长度请保持45个字符以下", curRow,
													titles.get(i));
											LOGGER.info("[数据导入] 数据导入失败： " + content);
											UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.DATA_MISMATCH,
													fileId, batchCode, dictCode, jobCode, content);
											fileLogs.add(fileLog);
											
											rowlist.set(i, value.substring(0, 10) + "***");
										}
										break;
									case SalaryConstants.DOUBLE_TYPE:
										Double.valueOf(value);
										
										if (!pattern.matcher(value).matches()) {
											effective = false;
											inState = 0;
											String content = String.format("第[%s]行[%s]的数据请保留两位小数", curRow,
													titles.get(i));
											LOGGER.info("[数据导入] 数据导入失败： " + content);
											UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.DATA_MISMATCH,
													fileId, batchCode, dictCode, jobCode, content);
											fileLogs.add(fileLog);
										}else {
											if(value.length()>13){
												effective = false;
												inState = 0;
												String content = String.format("第[%s]行[%s]的数据长度请保持12位有效数字以下", curRow,
														titles.get(i));
												LOGGER.info("[数据导入] 数据导入失败： " + content);
												UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.DATA_MISMATCH,
														fileId, batchCode, dictCode, jobCode, content);
												fileLogs.add(fileLog);
												
												rowlist.set(i, value.substring(0, 13));
						                    }
										}
										break;
									case SalaryConstants.DATE_TYPE:
										try {
											Double timeType = Double.valueOf(value);
											rowlist.set(i, sdf.format(HSSFDateUtil.getJavaDate(timeType)));
										} catch (Exception e) {
											Date stringType = ValueSelector.getDateValueForString(value);
											if(stringType == null) {
												throw new NumberFormatException();
											}
											rowlist.set(i, sdf.format(stringType));
										}
										break;
									default:
										break;
									}
								} catch (NumberFormatException e) {
									// 添加异常
									effective = false;
									inState = 0;
									String content = String.format("第[%s]行[%s]的数据类型与模板定义字段不匹配", curRow, titles.get(i));
									LOGGER.info("[数据导入] 数据导入失败： " + content);
									UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.DATA_MISMATCH, fileId,
											batchCode, dictCode, jobCode, content);
									fileLogs.add(fileLog);
								}
							}
						}
					}
				}

				// 校验整个批次内的工号，邮箱重复问题
				boolean isJobCode = salaryServiceCache.isExistJobCode(jobCode, fileId);
				if (isJobCode) {
					// 添加异常
					effective = false;
					inState = 0;
					String content = String.format("第%s行工号当前导入有重复,如果是更新将有可能导致数据丢失！", curRow);
					LOGGER.info("[数据导入] 数据导入失败： " + content);
					UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.NUMBER_REPEAT_EXCEPTION, fileId, batchCode,
							jobCodeColumn, jobCode, content);
					fileLogs.add(fileLog);

					rowlist.set(jobCodeIndex, jobCode + "(重复)");
				}

				boolean isNumber = salaryServiceCache.isExistNumber(rowlist.get(numberIndex), fileId);
				if (isNumber) {
					// 添加异常
					effective = false;
					inState = 0;
					String content = String.format("第%s行序号当前导入有重复", curRow);
					LOGGER.info("[数据导入] 数据导入失败： " + content);
					UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.NUMBER_REPEAT_EXCEPTION, fileId, batchCode,
							jobCodeColumn, jobCode, content);
					fileLogs.add(fileLog);
				}

				// 根据工号查询
				UserInfoDto userInfoDto = salaryUserDao.queryUserInfoByJobCode(jobCode);

				if (userInfoDto == null) {
					// 添加异常
					effective = false;
					inState = 0;
					String content = String.format("第%s行工号错误", curRow);
					LOGGER.info("[数据导入] 数据导入失败： " + content);
					UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.EPLOYEE_NOT_FOUND, fileId, batchCode,
							jobCodeColumn, jobCode, content);
					fileLogs.add(fileLog);
				} else {
					// 校验邮箱，
					if (emailIndex != -1) {
						if(rowlist.get(emailIndex).contains("hongte")){
							if (userInfoDto.getEmail() != null && !userInfoDto.getEmail().equals(rowlist.get(emailIndex))) {
								// 邮箱
								effective = false;
								inState = 0;
								String content = String.format("第%s行邮箱错误", curRow);
								LOGGER.info("[数据导入] 数据导入失败： " + content);
								UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.EPLOYEE_EMAIL_EXCEPTION, fileId,
										batchCode, emailColumn, jobCode, content);
								fileLogs.add(fileLog);
							}
						}


						// 校验整个批次内的工号，邮箱重复问题
						boolean isEmail = salaryServiceCache.isExistJobCode(rowlist.get(emailIndex), fileId);
						if (isEmail) {
							// 添加异常
							effective = false;
							inState = 0;
							String content = String.format("第%s行当前导入邮箱有重复", curRow);
							LOGGER.info("[数据导入] 数据导入失败： " + content);
							UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.NUMBER_REPEAT_EXCEPTION, fileId,
									batchCode, emailColumn, jobCode, content);
							fileLogs.add(fileLog);
//							rowlist.set(emailIndex, rowlist.get(emailIndex) + "(重复)");
						}
					}
					
					
					// 校验姓名，
					if (nameIndex != -1) {
						if(rowlist.get(emailIndex).contains("hongte")){
							//鸿特主体，规则不变
							if (userInfoDto.getUserName() != null && !userInfoDto.getUserName().equals(rowlist.get(nameIndex))) {
								// 姓名
								effective = false;
								inState = 0;
								String content = String.format("第%s行姓名错误", curRow);
								LOGGER.info("[数据导入] 数据导入失败： " + content);
								UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.USERNAME_EXCEPTION, fileId,
										batchCode, emailColumn, jobCode, content);
								fileLogs.add(fileLog);
							}
						}else {
							String namePinYin = HanyuPinyinHelper.toHanyuPinyin(rowlist.get(nameIndex));
							if(!(getNameFromEmail(rowlist.get(emailIndex)).equals(namePinYin))){
								// 姓名
								effective = false;
								inState = 0;
								String content = String.format("第%s行姓名错误", curRow);
								LOGGER.info("[数据导入] 数据导入失败： " + content);
								UploadFileLog fileLog = buildFileLog(UploadFileLogEnum.USERNAME_EXCEPTION, fileId,
										batchCode, emailColumn, jobCode, content);
								fileLogs.add(fileLog);
							}
						}

					}
				}

				if (inState == SalaryConstants.SALARY_YES) {
					successNo++;
				} else {
					failNo++;
				}
				rowlist.add(String.valueOf(inState));
				
				
				List<String> contentNode = new ArrayList<>();
				contentNode.addAll(rowlist);
				if(req.getFileId() != null) {// 上传的是错误数据，通过fileId更新或新增
					// 通过模板对应的表与工号与 fileId查询是否存在，
					DictRecordBaseDto dictRecordBaseDto = salaryBusinessDictDao.selectDictRecordIsExist(jobCode, fileId, tableName);
					
					if (dictRecordBaseDto != null) {
						// 更新
						updateContent.add(contentNode);
					} else {
						// 新增
						insertContent.add(contentNode);
					}
				}else {
					// 只能新增
					insertContent.add(contentNode);
				}
				rowlist.clear();
			}

			@Override
			public void processBatchData(List<List<String>> content, String type) throws SAXException {
				int jobCodeIndex = titles.indexOf(SalaryConstants.SALARY_JOB_CODE_NAME);
				List<Integer> paramIndex = new ArrayList<>();

				StringBuilder sql = new StringBuilder();
				// 生成sql查询数据,
				if (type.equals(SALARY_IMPORT_UPDATE)) {
					// 关联导入， 批量更新
					for (int i = 0; i < titles.size(); i++) {
						for (DictParamsDto dictParams : dictParams) {

							if (titles.get(i).equals(dictParams.getDictName())) {
								if (sql.length() == 0) {
									sql.append("update ").append(tableName).append(" set ");
									sql.append(dictParams.getDictColumn()).append("= ? ");
								} else {
									sql.append(",").append(dictParams.getDictColumn()).append("= ? ");
								}
								paramIndex.add(i);
								
							}
						}

					}

					// 保存base表信息

					if (sql.length() != 0) {
						sql.append(", in_state =? where batch_code = ? and job_code = ? and file_id = ? ");
						salaryBusinessDictDao.updateBatchBusinessDict(paramIndex, sql.toString(), content, batchCode,
								fileId, jobCodeIndex, stateIndex);
					} 

				} else {
					paramIndex.add(jobCodeIndex);
					// 新增导入
					sql.append("insert into ").append(tableName).append(" (file_id ,batch_code, in_state, job_code ");
					
					for (int i = 0; i < titles.size(); i++) {
						for (DictParamsDto dictParams : dictParams) {

							if (titles.get(i).equals(dictParams.getDictName())) {
								sql.append(", ").append(dictParams.getDictColumn());
								paramIndex.add(i);
							}
						}
					}

					if (sql.length() != 0) {
						sql.append(" ) values (?, ?, ?");
						for (int i = 0; i < paramIndex.size(); i++) {
							sql.append(", ?");
						}
						sql.append(")");
						salaryBusinessDictDao.insertBatchBusinessDict(paramIndex, sql.toString(), content, fileId,
								batchCode, stateIndex);
					}
				}

				// 清空content
				content.clear();

			}
		};

		try {
			xlx.process(req.getInputStream());
			LOGGER.info("[导入基础数据] 文件[{}]导入成功", fileId);
			return Result.buildSuccess(fileId);
		} catch (CustomResultException e) {
			LOGGER.error("[导入基础数据] 文件[{}]导入失败, 具体请查看记录!", fileId);
			Result<Integer> result = new Result<>();
			result.setReturnCode(ReturnCodeEnum.EXCEL_RESULT.getReturnCode());
			result.setMsg(e.getErrorMsg());
			result.setData(fileId);
			return result;
		}

	}
	
	

	protected UploadFileLog buildFileLog(UploadFileLogEnum dataEnum, Integer fileId, String batchCode, String dictCode,
			String jobCode, String content) {

		return new UploadFileLog(dataEnum, fileId, batchCode, dictCode, jobCode, content);
	}

	@Override
	public List<UploadFileLog> getSalaryFileLog(Integer index, Integer fileId) {
		return salaryServiceCache.getUploadFileLog(fileId);
	}

	@Override
	public List<DictParamsDto> getDictParamsByTemplateCode(String templateCode) {
		return salaryTemplateDetailDao.selectDictParamsByTemplateCode(templateCode);
	}

	@Transactional
	@Override
	public SXSSFWorkbook downloadSalaryFile(ExcelExportQuerySqlReq reqParam, Template template) throws Exception {
		List<ExcelBean> excelBeans = new ArrayList<>();
		// 创建表头信息， 对应dict_code字段
		List<DictParamsDto> fileList = salaryTemplateDetailDao.selectDictParamsByTemplateCode(reqParam.getTemplateCode());
		String tableName = SalaryTemplateTableEnum.getByTemplateType(template.getTemplateType()).getRelationTable();
		
		ExcelExportQuerySqlReq sqlCount = new ExcelExportQuerySqlReq();
		sqlCount.setTableName(tableName);
		sqlCount.setJobCodes(reqParam.getJobCodes());
		sqlCount.setBatchCode(reqParam.getBatchCode());
		sqlCount.setKeyworld(reqParam.getKeyworld());
		sqlCount.setDictParams(fileList);
		sqlCount.setInState(SalaryConstants.SALARY_YES);
		String countSql = sqlCount.getQueryConutSql();
		
		if (fileList != null) {
			
			for (DictParamsDto dictParamsDto : fileList) {
				excelBeans.add(new ExcelBean(dictParamsDto.getDictName(), dictParamsDto.getDictCode(), 0,
						dictParamsDto.getDictDataType()));
			}
			return PoiUtils.createExcelFile(excelBeans, new ExcelExportHandler() {

				@Override
				public int getExcelDataCount() throws Exception {
					return salaryBusinessDictDao.getExcelDataCount(countSql);
				}

				@Override
				public List<Map<String, Object>> getExcelDataByPage(int currentPage, int pageSize) throws Exception {
					sqlCount.setPage(currentPage);
					sqlCount.setLimit(pageSize);
					String dataSql = sqlCount.getQueryDataLimitSql();
					return salaryBusinessDictDao.getExcelDataLimit(dataSql);
				}

			});
		}
		return null;
	}

	@Override
	public Result validateUpload(SalaryUploadParam req) {
		try {
			validateHandler.validateSalaryUploadFile(req);
		} catch (ParamException e) {
			return Result.buildFail(e.getErrorMsg());
		}
		return Result.buildSuccess();
	}

	@Transactional
	@Override
	public SXSSFWorkbook exportErrorData(UploadFile file) throws Exception {
		Template template = salaryTemplateDao.selectTemplateByTemplateCode(file.getTemplateCode());
		
		String tableName = SalaryTemplateTableEnum.getByTemplateType(template.getTemplateType()).getRelationTable();
		List<ExcelBean> excelBeans = new ArrayList<>();
		// 创建表头信息， 对应dict_code字段
		List<DictParamsDto> fileList = salaryTemplateDetailDao.selectDictParamsByTemplateCode(file.getTemplateCode());
		String countSql = new ExcelExportQuerySqlReq(tableName, file.getFileId(), SalaryConstants.SALARY_NO).getQueryConutSql();
		
		if (fileList != null) {
			for (DictParamsDto dictParamsDto : fileList) {
				excelBeans.add(new ExcelBean(dictParamsDto.getDictName(), dictParamsDto.getDictCode(), 0,
						dictParamsDto.getDictDataType()));
			}
			return PoiUtils.createExcelFile(excelBeans, new ExcelExportHandler() {

				@Override
				public int getExcelDataCount() throws Exception {
					return salaryBusinessDictDao.getExcelDataCount(countSql);
				}

				@Override
				public List<Map<String, Object>> getExcelDataByPage(int currentPage, int pageSize) throws Exception {
					ExcelExportQuerySqlReq querySqlDto = new ExcelExportQuerySqlReq(tableName, file.getFileId(), SalaryConstants.SALARY_NO);
					querySqlDto.setPage(currentPage);
					querySqlDto.setLimit(pageSize);
					querySqlDto.setDictParams(fileList);
					String dataSql = querySqlDto.getQueryDataLimitSql();
					
					return salaryBusinessDictDao.getExcelDataLimit(dataSql);
				}

			});
		}
		return null;
	}

	private static String getNameFromEmail(String email){
		return email.split("@")[0];
	}


	public static void main(String[] args) {
		System.out.println(HanyuPinyinHelper.toHanyuPinyin("曾慧贤"));
		// liruntian
//		System.out.println(getNameFromEmail("liruntian@xianzhiqin.com"));
	}
}
